package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import model.Runner;

import utility.Utility;

public class Database {

	private static Connection MY_SQL_CONNECTION ; 

	public static void main(String[] arguments) {
		//            String url = "jdbc:oracle:thin:@dwops.wnyric.org:1521:escprod" ;
		//            String username = "REPORTCARDS" ;
		//            String password = "WNYRICWHS" ;
		//            Connection mainReportCardConnection = createConnection(DatabaseType.ORACLE, url, username, password) ;
		Properties userProperties = Utility.getProperties() ; 
		
		Connection mySqlConnection = fetchMySqlConnection(userProperties) ;
		boolean success = pingConnection(mySqlConnection, DatabaseType.MYSQL) ;
		if(success) {
			System.out.println("ping successful for " + "main my sql connection") ;
		}
		else {
			System.out.println("ping failed for " + "main my sql connection") ;
		}
	}

	public static Connection fetchMySqlConnection(Properties userProperties) {
		String databaseType = (String)userProperties.get("databaseType") ;
		String databaseUser = (String)userProperties.get("databaseUsername") ; 
		String databasePassword = (String)userProperties.get("databasePassword") ;
		System.out.println("databaseType: " + databaseType + ", databasePassword: " + databasePassword) ; 
		try {
			if(MY_SQL_CONNECTION == null || MY_SQL_CONNECTION.isClosed()) {
				MY_SQL_CONNECTION = createConnection(databaseType, // Connection Type
						"jdbc:mysql://localhost",  // URL 
						databaseUser,                    // username
						databasePassword
//						"RDOPm413"                   // password
				) ;
			}
		}
		catch(SQLException sqlException) {
			System.out.println(sqlException.getMessage()) ;
		}
		return MY_SQL_CONNECTION ;
	}

// This should not be called since it contains hardcoded connection information. User fetchMySqlConnection(Properties) instead.
	@Deprecated
	public static Connection fetchMySqlConnection() {
		String userName = Utility.getProperties().getProperty("databaseUser") ; 
		String password = Utility.getProperties().getProperty("databasePassword") ; 
		try {
			if(MY_SQL_CONNECTION == null || MY_SQL_CONNECTION.isClosed()) {
                MY_SQL_CONNECTION = createConnection(DatabaseType.MYSQL, // Connection Type
                        "jdbc:mysql://localhost",  // URL 
                        userName,                    // username
                        password                   // password
                        ) ;
			}
		}
		catch(SQLException sqlException) {
			System.out.println(sqlException.getMessage()) ;
		}
		return MY_SQL_CONNECTION ;
	}

	public static Connection createConnection(String databaseType, String url, String userName, String password) {
		Connection connection = null ;
		String driverClassName = "" ;
		if(DatabaseType.DB2.equals(databaseType)) {
			driverClassName = "com.ibm.db2.jcc.DB2Driver" ;
		}
		else if(DatabaseType.POSTGRES.equals(databaseType)) {
			driverClassName = "org.postgresql.Driver" ;
		}
		else if(DatabaseType.ORACLE.equals(databaseType)) {
			driverClassName = "oracle.jdbc.driver.OracleDriver" ;
		}
		else if(DatabaseType.MYSQL.equals(databaseType)) {
			driverClassName = "com.mysql.jdbc.Driver" ;
		}
		else {
			return null ; // invalid database type
		}


		try {
			Class.forName(driverClassName);
			connection = DriverManager.getConnection(
					url,
					userName,
					password ) ;
		}
		catch (SQLException sqlException) {
			sqlException.printStackTrace();
		}
		catch (ClassNotFoundException classNotFoundException) {
			System.out.println("database driver not found") ;
			classNotFoundException.printStackTrace() ;
		}
		return connection ;
	}

	/**
	 * Returns true when the connection has been verified as open
	 * @param connection
	 * @param databaseType
	 * @return
	 */
	public static boolean pingConnection(Connection connection, String databaseType) {
		String pingSql = "" ;
		if(DatabaseType.DB2.equals(databaseType)) {
			pingSql = "select 1 from SYSIBM.SYSDUMMY1 " ;
		}
		else if(DatabaseType.POSTGRES.equals(databaseType)) {
			pingSql = "select version()" ;
		}
		else if(DatabaseType.ORACLE.equals(databaseType)) {
			pingSql = "select 1 from DUAL " ;
		}
		else if(DatabaseType.MYSQL.equals(databaseType)) {
			pingSql = "select 1" ;
		}
		else {
			return false ; // invalid database type
		}

		boolean isConnected = true ;
		try {
			Statement pingStatement =  connection.createStatement( ) ;
			isConnected = pingStatement.execute(pingSql) ;
		}
		catch (SQLException exception) {
			exception.printStackTrace();
			isConnected = false ;
		}
		return isConnected ;
	}

	/**
	 * Handles all my open, close, and exception for a routine fetch call
	 * @param sqlFetchString
	 * @param connection
	 * @return
	 */
	public static ResultSet fetch(String sqlFetchString, Connection connection) {
		ResultSet results = null ; 
		Statement fetchStatement = null ; 
		try {
			fetchStatement = connection.createStatement() ;
			results = fetchStatement.executeQuery(sqlFetchString) ;
			return results ; 
		}
		catch (SQLException exception) {
			System.out.println("sql error in fetch. Sql: " + sqlFetchString) ; 
			exception.printStackTrace();
		} 
		return results ; 
	}

}
